import { getObjType } from "../utils/util";
import { isRealNum, isRealNull, valueIsError } from "./validate";
import { update } from "./format";
import formula from "./formula";
import dayjs from "dayjs";

const error = {
  v: "#VALUE!", //错误的参数或运算符
  n: "#NAME?", //公式名称错误
  na: "#N/A", //函数或公式中没有可用数值
  r: "#REF!", //删除了由其他公式引用的单元格
  d: "#DIV/0!", //除数是0或空单元格
  nm: "#NUM!", //当公式或函数中某个数字有问题时
  nl: "#NULL!", //交叉运算符（空格）使用不正确
  sp: "#SPILL!", //数组范围有其它值
};

const func_methods = {
  //单元格数据生成一维数组
  getCellDataArr: function(rangeObj, nullCellType, isNeglectNullCell) {
    let dataArr = [];

    if (rangeObj.data == null) {
      if (!isNeglectNullCell) {
        if (nullCellType === "number") {
          dataArr.push(0);
        } else if (nullCellType === "text") {
          dataArr.push("");
        }
      }
    } else {
      if (getObjType(rangeObj.data) === "array") {
        for (let i = 0; i < rangeObj.data.length; i++) {
          for (let j = 0; j < rangeObj.data[i].length; j++) {
            let cell = rangeObj.data[i][j];
            let value = cell;

            if (getObjType(cell) === "object") {
              value = cell.v;
            }

            if (value == null) {
              if (!isNeglectNullCell) {
                if (nullCellType === "number") {
                  value = 0;
                } else if (nullCellType === "text") {
                  value = "";
                }

                dataArr.push(value);
              }
            } else {
              dataArr.push(value);
            }
          }
        }
      } else {
        if (isRealNull(rangeObj.data.v)) {
          if (!isNeglectNullCell) {
            if (nullCellType == "number") {
              dataArr.push(0);
            } else if (nullCellType == "text") {
              dataArr.push("");
            }
          }
        } else {
          dataArr.push(rangeObj.data.v);
        }
      }
    }

    return dataArr;
  },
  //单元格数据生成二维数组
  getCellDataDyadicArr: function(rangeObj, nullCellType) {
    let dataArr = [];

    if (rangeObj.data == null) {
      let rowArr = [];

      if (nullCellType == "number") {
        rowArr.push(0);
      } else if (nullCellType == "text") {
        rowArr.push("");
      }

      dataArr.push(rowArr);
    } else {
      if (getObjType(rangeObj.data) == "array") {
        for (let i = 0; i < rangeObj.data.length; i++) {
          let rowArr = [];

          for (let j = 0; j < rangeObj.data[i].length; j++) {
            let cell = rangeObj.data[i][j];
            let value = cell;

            if (getObjType(cell) === "object") {
              value = cell.v;
            }

            if (value == null) {
              if (nullCellType === "number") {
                value = 0;
              } else if (nullCellType === "text") {
                value = "";
              }
            }

            rowArr.push(value);
          }

          dataArr.push(rowArr);
        }
      } else {
        let rowArr = [];

        let value = rangeObj.data.v;

        if (isRealNull(value)) {
          if (nullCellType == "number") {
            value = 0;
          } else if (nullCellType == "text") {
            value = "";
          }
        }

        rowArr.push(value);

        dataArr.push(rowArr);
      }
    }

    return dataArr;
  },
  //数组数据生成一维数组
  getDataArr: function(arr, isNeglectNaN) {
    let dataArr = [];

    if (isNeglectNaN == null) {
      isNeglectNaN = false;
    }

    if (getObjType(arr[0]) == "array") {
      for (let i = 0; i < arr.length; i++) {
        for (let j = 0; j < arr[i].length; j++) {
          if (isNeglectNaN && !isRealNum(arr[i][j])) {
            continue;
          }

          dataArr.push(arr[i][j]);
        }
      }
    } else {
      for (let i = 0; i < arr.length; i++) {
        if (isNeglectNaN && !isRealNum(arr[i])) {
          continue;
        }

        dataArr.push(arr[i]);
      }
    }

    return dataArr;
  },
  getDataDyadicArr: function(arr) {
    let dataArr = [];

    if (getObjType(arr[0]) == "array") {
      for (let i = 0; i < arr.length; i++) {
        let rowArr = [];

        for (let j = 0; j < arr[i].length; j++) {
          rowArr.push(arr[i][j]);
        }

        dataArr.push(rowArr);
      }
    } else {
      let rowArr = [];

      for (let i = 0; i < arr.length; i++) {
        rowArr.push(arr[i]);
      }

      dataArr.push(rowArr);
    }

    return dataArr;
  },
  //是否是规则二维数组
  isDyadicArr: function(arr) {
    let isDyadic = true;

    if (arr.length > 1) {
      let collen = arr[0].length;

      for (let i = 1; i < arr.length; i++) {
        if (arr[i].length != collen) {
          isDyadic = false;
          break;
        }
      }
    }

    return isDyadic;
  },
  //获取单个单元格数据，数组第一个值
  getFirstValue: function(data, nullCellType) {
    let _this = this;

    if (nullCellType == null) {
      nullCellType = "number";
    }

    let value;

    if (getObjType(data) == "array") {
      if (getObjType(data[0]) == "array") {
        if (!_this.isDyadicArr(data)) {
          return error.v;
        }

        value = data[0][0];
      } else {
        value = data[0];
      }
    } else if (getObjType(data) == "object" && data.startCell != null) {
      if (data.data == null) {
        if (nullCellType == "number") {
          value = 0;
        } else if (nullCellType == "text") {
          value = "";
        }
      } else {
        let cell_r = window.luckysheetCurrentRow;
        let cell_c = window.luckysheetCurrentColumn;

        if (data.rowl == 1 && data.coll == 1) {
          value = data.data;

          if (value == null || isRealNull(value.v)) {
            if (nullCellType == "number") {
              value = 0;
            } else if (nullCellType == "text") {
              value = "";
            }
          } else {
            value = value.v;
          }
        } else {
          if (
            data.data[0][0].mc != null &&
            data.data[0][0].mc.rs == data.rowl &&
            data.data[0][0].mc.cs == data.coll
          ) {
            value = data.data[0][0];

            if (value == null || isRealNull(value.v)) {
              if (nullCellType == "number") {
                value = 0;
              } else if (nullCellType == "text") {
                value = "";
              }
            } else {
              value = value.v;
            }
          } else if (data.rowl == 1 || data.coll == 1) {
            let cellrange = formula.getcellrange(data.startCell);
            let str = cellrange.row[0],
              edr = str + data.rowl - 1,
              stc = cellrange.column[0],
              edc = stc + data.coll - 1;

            if (data.rowl == 1) {
              if (cell_c < stc || cell_c > edc) {
                return error.v;
              }

              value = data.data[0][cell_c - stc];
            } else if (data.coll == 1) {
              if (cell_r < str || cell_r > edr) {
                return error.v;
              }

              value = data.data[cell_r - str][0];
            }

            if (value == null || isRealNull(value.v) || value.mc != null) {
              if (nullCellType == "number") {
                value = 0;
              } else if (nullCellType == "text") {
                value = "";
              }
            } else {
              value = value.v;
            }
          } else {
            return error.v;
          }
        }
      }
    } else {
      value = data;
    }

    return value;
  },
  //获取单元格的逻辑值
  getCellBoolen: function(data) {
    let _this = this;

    let cumulative = _this.getFirstValue(data);

    if (valueIsError(cumulative)) {
      return cumulative;
    }

    if (getObjType(cumulative) == "boolean") {
    } else if (
      getObjType(cumulative) == "string" &&
      (cumulative.toLowerCase() == "true" ||
        cumulative.toLowerCase() == "false")
    ) {
      if (cumulative.toLowerCase() == "true") {
        cumulative = true;
      } else if (cumulative.toLowerCase() == "false") {
        cumulative = false;
      }
    } else if (isRealNum(cumulative)) {
      cumulative = parseFloat(cumulative);

      cumulative = cumulative == 0 ? false : true;
    } else {
      return error.v;
    }

    return cumulative;
  },
  //获取单元格的日期
  getCellDate: function(data) {
    let _this = this;

    let date_text;

    if (getObjType(data) == "array") {
      if (getObjType(data[0]) == "array") {
        if (!_this.isDyadicArr(data)) {
          return error.v;
        }

        date_text = data[0][0];
      } else {
        date_text = data[0];
      }
    } else if (getObjType(data) == "object" && data.startCell != null) {
      if (
        data.data == null ||
        getObjType(data.data) == "array" ||
        isRealNull(data.data.v)
      ) {
        return error.v;
      }

      date_text = data.data.v;

      if (data.data.ct != null && data.data.ct.t == "d") {
        date_text = update("YYYY-MM-DD h:mm:ss", date_text);
      }
    } else {
      date_text = data;
    }

    return date_text;
  },
  getCellrangeDate: function(data) {
    let _this = this;

    let date = [];

    if (getObjType(data) == "array") {
      if (getObjType(data[0]) == "array" && !_this.isDyadicArr(data)) {
        return error.v;
      }

      date = date.concat(_this.getDataArr(data, false));
    } else if (getObjType(data) == "object" && data.startCell != null) {
      if (data.data == null) {
        date.push(0);
      } else {
        if (getObjType(data.data) == "array") {
          for (let i = 0; i < data.data.length; i++) {
            for (let j = 0; j < data.data[i].length; j++) {
              if (data.data[i][j] != null && !isRealNull(data.data[i][j].v)) {
                let value = data.data[i][j].v;

                if (data.data[i][j].ct != null && data.data[i][j].ct.t == "d") {
                  value = update("YYYY-MM-DD h:mm:ss", value);
                }

                date.push(value);
              } else {
                date.push(0);
              }
            }
          }
        } else {
          let value = data.data.v;

          if (data.data.ct != null && data.data.ct.t == "d") {
            value = update("YYYY-MM-DD h:mm:ss", value);
          }

          date.push(value);
        }
      }
    } else {
      date.push(data);
    }

    return date;
  },
  //获取正则字符串（处理 . * ? ~* ~?）
  getRegExpStr: function(str) {
    return str
      .replace("~*", "\\*")
      .replace("~?", "\\?")
      .replace(".", "\\.")
      .replace("*", ".*")
      .replace("?", ".");
  },
  //阶乘
  factorial: function(num) {
    if (num == 0 || num == 1) {
      return 1;
    } else {
      return num * this.factorial(num - 1);
    }
  },
  //双阶乘
  factorialDouble: function(num) {
    if (num <= 0) {
      return 1;
    } else {
      return num * this.factorialDouble(num - 2);
    }
  },
  //总体方差
  variance: function(num_arr) {
    let sum = 0,
      count = 0;

    for (let i = 0; i < num_arr.length; i++) {
      let number = num_arr[i];

      sum += number;
      count++;
    }

    let avg = sum / count;

    let sum_variance = 0;

    for (let j = 0; j < num_arr.length; j++) {
      let number = num_arr[j];

      sum_variance += (number - avg) * (number - avg);
    }

    return sum_variance / count;
  },
  //样本方差
  variance_s: function(num_arr) {
    let sum = 0,
      count = 0;

    for (let i = 0; i < num_arr.length; i++) {
      let number = num_arr[i];

      sum += number;
      count++;
    }

    let avg = sum / count;

    let sum_variance = 0;

    for (let j = 0; j < num_arr.length; j++) {
      let number = num_arr[j];

      sum_variance += (number - avg) * (number - avg);
    }

    return sum_variance / (count - 1);
  },
  //总体标准偏差
  standardDeviation: function(num_arr) {
    let sum = 0,
      count = 0;

    for (let i = 0; i < num_arr.length; i++) {
      let number = num_arr[i];

      sum += number;
      count++;
    }

    let avg = sum / count;

    let sum_variance = 0;

    for (let j = 0; j < num_arr.length; j++) {
      let number = num_arr[j];

      sum_variance += (number - avg) * (number - avg);
    }

    return Math.sqrt(sum_variance / count);
  },
  //样本标准偏差
  standardDeviation_s: function(num_arr) {
    let sum = 0,
      count = 0;

    for (let i = 0; i < num_arr.length; i++) {
      let number = num_arr[i];

      sum += number;
      count++;
    }

    let avg = sum / count;

    let sum_variance = 0;

    for (let j = 0; j < num_arr.length; j++) {
      let number = num_arr[j];

      sum_variance += (number - avg) * (number - avg);
    }

    return Math.sqrt(sum_variance / (count - 1));
  },
  //是否是闰年
  isLeapYear: function(year) {
    return new Date(year, 1, 29).getMonth() === 1;
  },
  //2月是否有29
  feb29Between: function(date1, date2) {
    let _this = this;

    let year1 = dayjs(date1).year();
    let mar1year1 = dayjs().set({ year: year1, month: 2, date: 1 });

    if (
      _this.isLeapYear(year1) &&
      dayjs(date1) < dayjs(mar1year1) &&
      dayjs(date2) >= dayjs(mar1year1)
    ) {
      return true;
    }

    let year2 = dayjs(date2).year();
    let mar1year2 = dayjs().set({ year: year2, month: 2, date: 1 });

    return (
      _this.isLeapYear(year2) &&
      dayjs(date2) >= dayjs(mar1year2) &&
      dayjs(date1) < dayjs(mar1year2)
    );
  },
  //SQL 查询
  findResultIndex: function(database, criterias) {
    let matches = {};

    for (let i = 1; i < database[0].length; ++i) {
      matches[i] = true;
    }

    let maxCriteriaLength = criterias[0].length;

    for (i = 1; i < criterias.length; ++i) {
      if (criterias[i].length > maxCriteriaLength) {
        maxCriteriaLength = criterias[i].length;
      }
    }

    for (let k = 1; k < database.length; ++k) {
      for (let l = 1; l < database[k].length; ++l) {
        let currentCriteriaResult = false;
        let hasMatchingCriteria = false;

        for (let j = 0; j < criterias.length; ++j) {
          let criteria = criterias[j];

          if (criteria.length < maxCriteriaLength) {
            continue;
          }

          let criteriaField = criteria[0];

          if (database[k][0] !== criteriaField) {
            continue;
          }

          hasMatchingCriteria = true;

          for (let p = 1; p < criteria.length; ++p) {
            currentCriteriaResult =
              currentCriteriaResult ||
              new Function("return " + database[k][l] + criteria[p])(); // jshint ignore:line
          }
        }

        if (hasMatchingCriteria) {
          matches[l] = matches[l] && currentCriteriaResult;
        }
      }
    }

    let result = [];

    for (let n = 0; n < database[0].length; ++n) {
      if (matches[n]) {
        result.push(n - 1);
      }
    }

    return result;
  },
  findField: function(database, title) {
    let index = null;

    for (let i = 0; i < database.length; i++) {
      if (database[i][0] == title) {
        index = i;
        break;
      }
    }

    if (index == null) {
      return error.v;
    }

    return index;
  },
  rest: function(array, idx) {
    idx = idx || 1;

    if (!array || typeof array.slice !== "function") {
      return array;
    }

    return array.slice(idx);
  },
  compact: function(array) {
    if (!array) {
      return array;
    }

    let result = [];

    for (let i = 0; i < array.length; ++i) {
      if (!array[i]) {
        continue;
      }

      result.push(array[i]);
    }

    return result;
  },
};

export default func_methods;
